# -*- coding: utf-8 -*-

import numpy as np
import pandas as pd


def test():
    excel_file = pd.ExcelFile(r'2.xlsx')
    big_fund: pd.DataFrame = pd.read_excel(excel_file, sheet_name='big_fund')
    big_fund.drop(columns=['Unnamed: 0'], inplace=True)
    big_fund['sys_account_number'] = big_fund['sys_account_number'].apply(lambda x: str(x).zfill(6))
    strategy: pd.DataFrame = pd.read_excel(excel_file, sheet_name='strategy')
    strategy.drop(columns=['Unnamed: 0'], inplace=True)

    nav_df = pd.DataFrame()
    for i in range(len(big_fund)):
        fund_info = big_fund.iloc[i]
        port_code = fund_info['sys_account_number']
        nav_data: pd.DataFrame = pd.read_excel(excel_file, sheet_name=port_code)
        if nav_data.empty or len(nav_data) == 0:
            continue
        nav_data.drop(columns=['Unnamed: 0'], inplace=True)
        nav_data.drop(columns=['price_date'], inplace=True)
        start_nav = nav_data[nav_data['date_flag'] == 'start_date']
        end_nav = nav_data[nav_data['date_flag'] == 'end_date']
        start_nav.drop(columns=['date_flag'], inplace=True)
        end_nav.drop(columns=['date_flag'], inplace=True)
        nav_data = start_nav.merge(end_nav, on=['valuation_code'], suffixes=['_start', '_end'])
        nav_data = nav_data.merge(strategy, left_on='valuation_code', right_on='sub_portcode', how='left')
        nav_data['port_code'] = nav_data['valuation_code'].apply(lambda x: port_code)
        nav_df = pd.concat([nav_df, nav_data])
        pass
    nav_df = nav_df.merge(big_fund, left_on='port_code', right_on='sys_account_number', how='left')
    nav_df.to_excel(r'3.xlsx')
    pass


if __name__ == '__main__':
    test()
    pass
